package com.tledu.cyrus.dao.impl;

import com.tledu.cyrus.dao.IStudentDao;
import com.tledu.cyrus.domain.Student;
import com.tledu.cyrus.utils.JdbcUtils;
import com.tledu.cyrus.utils.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author cyrus
 */
public class StudentDaoImpl implements IStudentDao {
    @Override
    public List<Student> list(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();

        String sql = "select * from student where 1 = 1 ";
        // 组装查询条件
        if (StringUtils.isNotBlank(student.getName())) {
            sql += " and name like ?";
        }
        if (StringUtils.isNotBlank(student.getPhone())) {
            sql += " and phone = ?";
        }
        if (student.getSex() != null) {
            sql += " and sex = ?";
        }

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        // 设置查询条件的值
        if (student.getName() != null && !student.getName().isEmpty()) {
            preparedStatement.setString(i++, "%" + student.getName() + "%");
        }
        if (StringUtils.isNotBlank(student.getPhone())) {
            preparedStatement.setString(i++, student.getPhone());
        }
        if (student.getSex() != null) {
            preparedStatement.setInt(i++, student.getSex());
        }

        // 执行sql
        ResultSet rs = preparedStatement.executeQuery();
        // 封装结果
        List<Student> list = getStudentsFromResultSet(rs);

        preparedStatement.close();
        conn.close();
        return list;
    }

    @Override
    public List<Student> page(Student student, int pageNum, int pageSize) throws SQLException {
        Connection conn = JdbcUtils.getConnection();

        String sql = "select * from student where 1 = 1 ";
        // 组装查询条件
        if (StringUtils.isNotBlank(student.getName())) {
            sql += " and name like ?";
        }
        if (StringUtils.isNotBlank(student.getPhone())) {
            sql += " and phone = ?";
        }
        if (student.getSex() != null) {
            sql += " and sex = ?";
        }

        sql += " limit ?,?";

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        // 设置查询条件的值
        if (student.getName() != null && !student.getName().isEmpty()) {
            preparedStatement.setString(i++, "%" + student.getName() + "%");
        }
        if (StringUtils.isNotBlank(student.getPhone())) {
            preparedStatement.setString(i++, student.getPhone());
        }
        if (student.getSex() != null) {
            preparedStatement.setInt(i++, student.getSex());
        }

        preparedStatement.setInt(i++, (pageNum - 1) * pageSize);
        preparedStatement.setInt(i++, pageSize);

        // 执行sql
        ResultSet rs = preparedStatement.executeQuery();
        // 封装结果
        List<Student> list = getStudentsFromResultSet(rs);

        preparedStatement.close();
        conn.close();
        return list;
    }

    @Override
    public int count(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();

        String sql = "select count(*) as total from student where 1 = 1 ";
        // 组装查询条件
        if (StringUtils.isNotBlank(student.getName())) {
            sql += " and name like ?";
        }
        if (StringUtils.isNotBlank(student.getPhone())) {
            sql += " and phone = ?";
        }
        if (student.getSex() != null) {
            sql += " and sex = ?";
        }

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        // 设置查询条件的值
        if (student.getName() != null && !student.getName().isEmpty()) {
            preparedStatement.setString(i++, "%" + student.getName() + "%");
        }
        if (StringUtils.isNotBlank(student.getPhone())) {
            preparedStatement.setString(i++, student.getPhone());
        }
        if (student.getSex() != null) {
            preparedStatement.setInt(i++, student.getSex());
        }

        // 执行sql
        ResultSet rs = preparedStatement.executeQuery();
        // 封装结果
        int total = 0;
        while (rs.next()) {
            total = rs.getInt("total");
        }

        preparedStatement.close();
        conn.close();
        return total;
    }

    @Override
    public Student getStudent(int id) throws SQLException {
        // 获取连接
        Connection conn = JdbcUtils.getConnection();
        // 编写sql
        String sql = "select * from student where id = ?";
        // 创建sql执行对象
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        // 设置参数
        preparedStatement.setInt(1, id);
        // 执行sql
        ResultSet rs = preparedStatement.executeQuery();
        // 调用封装获取结果
        List<Student> list = getStudentsFromResultSet(rs);
        // 关闭连接
        preparedStatement.close();
        conn.close();
        // 判断有没有查到
        if (!list.isEmpty()) {
            // 返回查询到的内容
            return list.get(0);
        }
        // 没查到返回空
        return null;
    }

    @Override
    public int addStudent(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "insert into student (name, sex, age, phone) VALUES (?,?,?,?)";

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        preparedStatement.setString(i++, student.getName());
        preparedStatement.setInt(i++, student.getSex());
        preparedStatement.setInt(i++, student.getAge());
        preparedStatement.setString(i++, student.getPhone());

        int result = preparedStatement.executeUpdate();
        // 关闭连接
        preparedStatement.close();
        conn.close();
        return result;
    }

    @Override
    public int updateStudent(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "update student set name = ?  ,sex = ? , age = ?, phone = ? where id = ?";

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        preparedStatement.setString(i++, student.getName());
        preparedStatement.setInt(i++, student.getSex());
        preparedStatement.setInt(i++, student.getAge());
        preparedStatement.setString(i++, student.getPhone());

        preparedStatement.setInt(i++, student.getId());
        int result = preparedStatement.executeUpdate();
        // 关闭连接
        preparedStatement.close();
        conn.close();
        return result;
    }

    @Override
    public int deleteStudent(int id) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "delete from student where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        int result = preparedStatement.executeUpdate();
        // 关闭连接
        preparedStatement.close();
        conn.close();
        return result;
    }

    private List<Student> getStudentsFromResultSet(ResultSet rs) throws SQLException {
        // 执行sql
        List<Student> list = new ArrayList<Student>();
        while (rs.next()) {
            list.add(new Student(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getInt("age"),
                    rs.getInt("sex"),
                    rs.getString("phone")
            ));
        }
        return list;
    }
}
